insert overwrite table jms_dm.dm_taking_timely_detail_hi
select
     order_no                 --订单号
    ,waybill_no               --运单号
    ,order_source_code        --订单来源
    ,order_source_name        --订单来源
    ,config_id                --配置id
    ,sender_province_code     --寄件省编码
    ,sender_province_name     --寄件省名称
    ,sender_city_code         --寄件市编码
    ,sender_city_name         --寄件市名称
    ,sender_area_code         --寄件区编码
    ,sender_area_name         --寄件区名称
    ,sender_network_code      --寄件网点编码
    ,sender_network_name      --寄件网点名称
    ,sender_fran_code         --寄件加盟商编码
    ,sender_fran_name         --寄件加盟商名称
    ,sender_agent_code        --寄件代理区编码
    ,sender_agent_name        --寄件代理区名称
    ,sender_region_code       --寄件大区编码
    ,sender_region_name       --寄件大区名称
    ,sender_staff_code        --寄件业务员编码
    ,sender_staff_name        --寄件业务员名称
    ,order_source_group_code  --订单来源分类
    ,order_source_group_name  --订单来源分类
    ,customer_code            --客户编码
    ,customer_name            --客户名称
    ,order_status_code        --订单状态
    ,pick_time                --取件时间
    ,pick_end_time            --取件截止时间
    ,cancel_time              --订单取消时间
    ,input_time               --订单录入时间
    ,store_send_time          --下单时间
    ,plate_flag               --0或空值：订单录入时间（取号时间），1：商家点击发货时间
    ,case when if_tml_collect = 1 and store_send_time is not null and pick_time > pick_end_time then 1
          when if_tml_collect = 1 and store_send_time is not null and pick_time <= pick_end_time then 0
          when if_tml_collect = 1 and pick_time is not null and pick_time > pick_end_time
           and pick_time <= date_format(`current_timestamp`() , 'yyyy-MM-dd HH:mm:ss') then 1
          when if_tml_collect = 1 and pick_time is null or pick_time > date_format(`current_timestamp`(), 'yyyy-MM-dd HH:mm:ss') then 2
          when if_tml_collect = 0 and order_source_name = '极地湾' and jdw_if_cs = 1 then 3
          else 0 end taking_type --1延误揽收,2统计前未揽收,3未及时到中心
   ,case when order_source_name = '极地湾' and if_tml_collect = 0 and jdw_if_cs = 1 --极地湾规则特殊配置，到中心超时
         then 1 else if_tml_collect end as if_tml_collect --是否及时揽收1未及时,0及时
   ,if_dispatch    --是否调度
   ,if_taking_miss --是否揽收漏扫
   ,if_taking_diff --是否取件问题件
   ,diff_code
   ,diff_name
   ,taking_time --揽收时间
   ,start_center_arrival_time --首中心到件时间
   ,weight_value --权重
   ,last_dt as dt
from (
    select
         *
        ,case when pick_time is null or (pick_time > pick_end_time) then 1 else 0 end as if_tml_collect -- 是否及时揽收 1：未及时  0：及时
        ,nvl(date_format(pick_end_time, 'yyyy-MM-dd'), '9999-99-99') as last_dt
        ,case when order_source_name = '极地湾' and pick_time <= pick_end_time and start_center_arrival_time is null
              or nvl(unix_timestamp(start_center_arrival_time),unix_timestamp(current_timestamp())) - unix_timestamp(pick_time) > 86400 then 1 else 0 end as jdw_if_cs
        --订单来源为极地湾，及时揽收但是没收中心到件算为不及时
        ,config.weight_value --权重
    from (
        select
             der.order_no                    as order_no                 --订单号
            ,der.waybill_no                  as waybill_no               --运单号
            ,der.order_source_code           as order_source_code        --订单来源
            ,der.order_source_name           as order_source_name        --订单来源
            ,null                            as config_id                --配置id
            ,der.sender_province_id          as sender_province_code     --寄件省编码
            ,der.sender_province_name        as sender_province_name     --寄件省名称
            ,der.sender_city_id              as sender_city_code         --寄件市编码
            ,der.sender_city_name            as sender_city_name         --寄件市名称
            ,der.sender_area_id              as sender_area_code         --寄件区编码
            ,der.sender_area_name            as sender_area_name         --寄件区名称
            ,dim.code                        as sender_network_code      --寄件网点编码
            ,dim.name                        as sender_network_name      --寄件网点名称
            ,dim.fran_code                   as sender_fran_code         --寄件加盟商编码
            ,dim.fran_name                   as sender_fran_name         --寄件加盟商名称
            ,dim.agent_code                  as sender_agent_code        --寄件代理区编码
            ,dim.agent_name                  as sender_agent_name        --寄件代理区名称
            ,dim.regional_id                 as sender_region_code       --寄件大区编码
            ,dim.regional_desc               as sender_region_name       --寄件大区名称
            ,der.pick_staff_code             as sender_staff_code        --寄件业务员编码
            ,der.pick_staff_name             as sender_staff_name        --寄件业务员名称
            ,der_sou.order_source_group_id   as order_source_group_code  --订单来源分类
            ,der_sou.order_source_group_name as order_source_group_name  --订单来源分类
            ,der.customer_code               as customer_code            --客户编码
            ,der.customer_name               as customer_name            --客户名称
            ,der.order_status_code           as order_status_code        --订单状态
            ,der.pick_time                   as pick_time                --取件时间
            ,der.cancel_time                 as cancel_time              --订单取消时间
            ,der.input_time                  as input_time               --订单录入时间
            ,case when der_pick.plate_flag is not null and der_pick.plate_flag = 1 and der_exp.waybill_no is not null then from_unixtime(unix_timestamp(der_exp.input_time) + 86400, 'yyyy-MM-dd HH:mm:ss') --七星潭默认+1天
                  when der_pick.plate_flag is not null and der_pick.plate_flag<> 1 and der_pick.pick_end_time is not null then concat(date_add(to_date(der.input_time),der_pick.pick_add_day),' ',der_pick.pick_end_time) --配置了跨天用跨天规则
                  when der_pick.plate_flag is not null and der_pick.plate_flag<> 1 and der_pick.pick_end_time is null then from_unixtime(unix_timestamp(der.input_time) + (substr(der_pick.long_time,1,2) * 60 * 60) , 'yyyy-MM-dd HH:mm:ss') --配置了揽收时长用揽收时长的规则
                  else der.pick_end_time end pick_end_time -- 截止时间 默认比下单时间多一天
            ,case when der_pick.plate_flag = 1 and der_exp.waybill_no is not null then der_exp.input_time else null end store_send_time --下单时间
            ,der_pick.plate_flag --0或空值：订单录入时间（取号时间），1：商家点击发货时间
            ,case when (der.dispatch_staff_time is not null and der.back_dispatch_network_time is null) --调度业务员时间不为空且打回时间为空
                  or (der.back_dispatch_network_time is not null and der.dispatch_staff_time > der.back_dispatch_network_time) --调度业务员时间 > 打回时间
                  or der.need_dispatch = 1 --已调度
                  then 1 else 0 end as if_dispatch --是否调度
            ,case when miss.waybill_no is not null then 1 else 0 end as if_taking_miss --是否揽收漏扫
            ,case when abnormal.waybill_no is not null then 1 else 0 end as if_taking_diff --是否取件问题件
            ,abnormal.diff_code
            ,abnormal.diff_name
            ,tak.taking_time --揽收时间
            ,center_arr.start_center_arrival_time --首中心到件时间
            ,case when der.order_source_name <> '七星潭' then der.input_time else der_exp.input_time end as last_input_time --最终录入时间
            ,der.dt
        from (
            select
                 id as order_no
                ,waybill_id  as waybill_no
                ,order_status_code
                ,nvl(subscribe_source_code,order_source_code) as order_source_code --订单来源
                ,nvl(subscribe_source_name,order_source_name) as order_source_name --订单来源
                ,sender_province_id
                ,sender_province_name
                ,sender_city_id
                ,sender_city_name
                ,sender_area_id
                ,sender_area_name
                ,pick_network_code
                ,proxy_area_code
                ,proxy_area_name
                ,pick_staff_code
                ,pick_staff_name
                ,customer_code
                ,customer_name
                ,input_time
                ,pick_time
                ,pick_end_time --客渠的下单时间
                ,cancel_time
                ,dispatch_staff_time --调度业务员时间
                ,back_dispatch_network_time --打回调度时间
                ,need_dispatch --是否需要调度,1需要,2不需要
                ,dt
            from jms_dwd.dwd_yl_oms_oms_order_hf
            where dt >= date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd-HH')
            and dt < date_format(date_add('{{ execution_date | cst_ds }}', 1 ), 'yyyy-MM-dd-HH')
        ) der --小时订单表
        left join jms_dim.dim_network_whole_massage dim --网点维度
           on der.pick_network_code = dim.code
        left join jms_dim.dim_tab_order_source_group_hi der_sou --订单来源分类表
            on der.order_source_code = der_sou.order_source_code
        left join (
            select
                 order_source_code
                ,order_source_name
                ,sender_province_id
                ,plate_flag
                ,start_time
                ,end_time
                ,pick_end_time
                ,pick_add_day
                ,long_time --时长
            from jms_dim.dim_tab_order_pick_rate_hi  -- 关联及时揽收表  商家发货判定的来源
            where is_delete = 1
            --and plate_flag = 1 --plate_flag为1表示配置
        ) der_pick on der.order_source_code = der_pick.order_source_code
              and der.sender_province_id = der_pick.sender_province_id
              and substr(der.input_time,12,8)  between der_pick.start_time and der_pick.end_time
        left join (
            select
                mail_no as waybill_no --运单号
               ,min(input_time) as input_time --商家发货时间
            from jms_dwd.dwd_yl_oms_order_tb_exporder_hi --线下订单表
            where dt >= date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd-HH')
              and dt < date_format(date_add('{{ execution_date | cst_ds }}',1 ), 'yyyy-MM-dd-HH')
              and mail_no like 'JT%'
            group by mail_no
        ) der_exp on der_exp.waybill_no = der.waybill_no
        left join (
            select
                billcode as waybill_no
            from jms_dm.dm_taking_scan_miss_detail  --网点漏扫
            where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
            and is_taking_missed_scan = 1 --揽收漏扫
        ) miss on miss.waybill_no = der.waybill_no
        left join (
            select
                 waybill_no
                ,scan_time as taking_time
            from (
                select
                     waybill_no
                    ,scan_time
                    ,row_number() over(partition by waybill_no order by scan_time asc) as rn
                from jms_dwd.dwd_tab_barscan_taking_base_hi --小时揽收表
                where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
            ) a where a.rn = 1
        ) tak on tak.waybill_no = der.waybill_no
        left join (
            select
                 waybill_no
                ,scan_time as start_center_arrival_time
            from (
                select
                     waybill_no
                    ,scan_time
                    ,row_number() over(partition by waybill_no order by scan_time asc) as rn
                from jms_dwd.dwd_tab_barscan_centerarrival_base_hi --小时中心到件表
                where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
            ) a where a.rn = 1
        ) center_arr on center_arr.waybill_no = der.waybill_no
        left join (
            select
                 a.waybill_no
                ,b.code diff_code
                ,b.name diff_name
            from (
                select
                     waybill_no
                    ,problem_type_code
                    ,row_number() over(partition by waybill_no order by scan_time asc) as rn
                from jms_dwd.dwd_tab_barscan_difficult_base_hi --问题件表
                where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}', 6) and '{{ execution_date | cst_ds }}'
            ) a
            inner join jms_dim.dim_lmdm_sys_abnormal_piece b
              on a.problem_type_code = b.code and find_in_set('1', b.op_type) > 0
            where a.rn = 1
        ) abnormal on abnormal.waybill_no = der.waybill_no
        left join (
            select
                 source_name
                ,source_code
                ,neteork_code
                ,neteork_name
                ,agent_code
                ,agent_name
                ,status
            from jms_dim.dim_tab_special_store_cfg_base
            where status = 1
        ) spec on der.order_source_name = spec.source_name
              and der.pick_network_code = spec.neteork_code
        where der.dt >= date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd-HH')
          and der.dt < date_format(date_add('{{ execution_date | cst_ds }}', 1 ), 'yyyy-MM-dd-HH')
          and if(der.order_source_code = 'D801', der_exp.input_time is not null, 1 = 1)
          and (nvl(der.order_status_code, -1) != 104 or der.cancel_time > concat(date(date_add('{{ execution_date | cst_ds }}', 0)), ' 08:00:00'))
          and (upper(der.pick_network_code) <> 'TEST001' or upper(dim.parent_network_code) <> 'TEST001')
          and upper(nvl(der.proxy_area_code, '*^^*')) <> 'TEST001'
          and spec.neteork_code is null --剔除业务配置的网点
    ) a
    left join (
        select
             source_name  --订阅来源名称
            ,source_code  --订阅来源编码
            ,min_time     --发揽时间段最小值
            ,max_time     --发揽时间段最大值
            ,weight_value --权重
        from jms_dim.dim_tab_collect_weight_config_base
        where status = 1 
    ) config on config.source_name = a.order_source_name
            and (unix_timestamp(taking_time) - unix_timestamp(last_input_time)) / 60 / 60 >= config.min_time 
            and (unix_timestamp(taking_time) - unix_timestamp(last_input_time)) / 60 / 60 <  config.max_time
) a where last_dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
and sender_network_code <> '888888'
and sender_fran_code <> '888888'
and sender_agent_code <> '888888'
distribute by pmod(hash(rand()),80);